package xc.doughnut.service;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import xc.doughnut.util.MySQLGenerator;

import java.sql.*;
import java.util.*;

/**
 * @className: IMySQLService
 * @package: xc.doughnut.service
 * @author: XuChao
 * @description: 获取Connection连接对象
 * @date: 2023/3/31 11:30
 */
@Slf4j
@Data
//@Configuration
//@ConfigurationProperties(prefix = "doughnut.mysql.config")
public class IMySQLService {

    /**
     * 驱动名
     */
    private String driverClassName = "com.mysql.cj.jdbc.Driver";

    /**
     * ip地址
     */
    private String ip = MySQLGenerator.DATABASE_IP;

    /**
     * 端口
     */
    private String port = MySQLGenerator.MYSQL_DB_PORT;

    /**
     * 数据库名
     */
    private String dbName = MySQLGenerator.MYSQL_DATABASE_NAME;

    /**
     * 用户名
     */
    private String username = MySQLGenerator.MYSQL_DB_USERNAME;

    /**
     * 密码
     */
    private String password = MySQLGenerator.MYSQL_DB_PASSWORD;

    /**
     * url?_&_&_
     */
    private String urlSuffix = MySQLGenerator.URL_SUFFIX;

    public IMySQLService() {}

    public IMySQLService(String dbName) {
        if (Objects.nonNull(dbName)) this.dbName = dbName;
    }
    public IMySQLService(String driverClassName, String ip, String port, String dbName,
                         String username, String password, String urlSuffix) {
        if (Objects.nonNull(driverClassName)) this.driverClassName = getDriverClassName();
        if (Objects.nonNull(ip)) this.ip = ip;
        if (Objects.nonNull(port)) this.port = port;
        if (Objects.nonNull(dbName)) this.dbName = dbName;
        if (Objects.nonNull(username)) this.username = username;
        if (Objects.nonNull(password)) this.password = password;
        if (Objects.nonNull(urlSuffix)) this.urlSuffix = urlSuffix;
    }

    /**
     * 获取Connection连接
     */
    public Connection getConnection() {
        try {
            // 注册 JDBC 驱动
            Class.forName(this.driverClassName);
            StringBuilder url = new StringBuilder("jdbc:mysql://");
            // "jdbc:mysql://localhost:3306/user";
            url.append(this.ip).append(":").append(this.port).append("/").append(this.dbName);
            if (this.urlSuffix != null && !"".equals(this.urlSuffix)) {
                url.append("?").append(this.urlSuffix);
            }
            Connection c = DriverManager.getConnection(url.toString(), this.username, this.password);
            log.info("获取连接:" + c);
            // 返回连接
            return c;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("连接失败 " + e.getMessage());
            return null;
        }
    }

    /**
     * 获取数据库多个表的所有字段
     */
    public Map<String, List<String>> getTablesFields(String[] tableNames) {
        return this.getTablesFieldsWithDb(this.dbName, tableNames);
    }

    /**
     * 获取数据库多个表的所有字段,指定数据库
     */
    public Map<String, List<String>> getTablesFieldsWithDb(String dataBase, String[] tableNames) {
        if (tableNames == null || tableNames.length == 0) {
            throw new NullPointerException("tableNames禁止为 null和' '");
        }
        // 去除空项
        tableNames = Arrays.stream(tableNames).filter(t -> !"".equals(t)).toArray(String[]::new);
        Map<String, List<String>> dataMap = new HashMap<>(tableNames.length);
        for (String tableName : tableNames) {
            List<String> tableField = this.getTableField(dataBase, tableName);
            dataMap.put(tableName, tableField);
        }
        return dataMap;
    }

    /**
     * 获取数据库单个表的所有字段
     */
    public List<String> getTableField(String tableName) {
        return this.getTableField(this.dbName, tableName);
    }

    /**
     * 获取数据库单个表的所有字段,指定数据库名称
     */
    public List<String> getTableField(String dataBase, String tableName) {
        Connection c = this.getConnection();
        try {
            DatabaseMetaData metaData = c.getMetaData();
            ResultSet resultSet = metaData.getColumns(dataBase, null, tableName, null);
            ArrayList<String> columns = new ArrayList<>();
            boolean dbNameFlag = true;
            boolean tNameFlag = true;

            while (resultSet.next()) {
                if (dbNameFlag) {
                    // 数据库名
                    String dbName = resultSet.getString(".TABLE_CAT");
                    System.out.println("[库名" + dbName + "]");
                    dbNameFlag = false;
                }
                if (tNameFlag) {
                    // 表名
                    String tName = resultSet.getString(".TABLE_NAME");
                    System.out.println("↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓[表名" + tName + "]" + "↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓");
                    tNameFlag = false;
                }
                // 获取字段名
                String field = resultSet.getString(".COLUMN_NAME");
                // 获取字段类型
                String fieldType = resultSet.getString(".TYPE_NAME");
                String fieldLength = resultSet.getString(".COLUMN_SIZE");
                String fieldDesc = resultSet.getString(".REMARKS");
                String info = String.format("[字段:%s 类型:%s 长度:%s 注释:%s]",
                        field, fieldType, fieldLength, "".equals(fieldDesc) ? "''" : fieldDesc);
                System.out.println(info);
                columns.add(field);
            }
            if (!tNameFlag) {
                System.out.println("↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑");
            }
            return columns;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return Collections.emptyList();
        } finally {
            this.closeConnection(c);
        }
    }

    /**
     * 获取所有的数据库
     */
    public List<String> getDataBases() {
        Connection c = this.getConnection();
        try {
            DatabaseMetaData metaData = c.getMetaData();
            ResultSet catalogs = metaData.getCatalogs();
            ArrayList<String> dbs = new ArrayList<>();
            while (catalogs.next()) {
                String db = catalogs.getString(".TABLE_CAT");
                dbs.add(db);
            }
            return dbs;
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        } finally {
            this.closeConnection(c);
        }

    }

    /**
     * 获取当前数据库的所有表
     */
    public List<String> getTables() {
        return this.getTables(this.dbName);
    }

    /**
     * 获取数据库中表,返回数据格式String[]
     */
    public String[] getStrArrTables() {
        List<String> tables = this.getTables(this.dbName);
        return tables.stream().toArray(String[]::new);
    }

    /**
     * 获取指定数据库的所有表
     */
    public List<String> getTables(String dataBase) {
        Connection c = this.getConnection();
        try {
            DatabaseMetaData metaData = c.getMetaData();
            // 最后一个参数TABLE 表示用户表 见 DatabaseMetaData.getTableTypes()方法
            ResultSet resultSet = metaData.getTables(dataBase, null, null, new String[]{"TABLE"});
            ArrayList<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                String table = resultSet.getString("TABLE_NAME");
                tables.add(table);
            }
            return tables;
        } catch (SQLException e) {
            e.printStackTrace();
            return Collections.emptyList();
        } finally {
            this.closeConnection(c);
        }
    }

    /**
     * 关闭连接
     */
    private void closeConnection(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
                log.info("连接关闭:" + connection);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("连接关闭失败:" + e.getMessage());
        }
    }

}
